DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`(
`bid` VARCHAR(100) NOT NULL PRIMARY KEY COMMENT '图书编号',
`bName` VARCHAR(100) NOT NULL COMMENT '书名',
`suthor` VARCHAR(100) COMMENT '作者',
`pubComp` VARCHAR(100) COMMENT '出版社',
`pubDate` date COMMENT '出版日期',
`bCount` INT(6) COMMENT '现存数量',
`price` FLOAT(10) COMMENT '单价'
)COMMENT '图书信息表';
DROP TABLE IF EXISTS `reader`;
CREATE TABLE `reader`(
`rid` VARCHAR(100) NOT NULL PRIMARY KEY COMMENT '读者编号',
`rName` VARCHAR(100) NOT NULL COMMENT '读者姓名',
`lendNum` INT(10) COMMENT '以借书数量',
`rAddress` VARCHAR(100) COMMENT '联系地址'
)COMMENT '读者信息表';
DROP TABLE IF EXISTS `borrow`;
CREATE TABLE `borrow`(
`rid` VARCHAR(100) NOT NULL COMMENT '读者编号',
`nif` VARCHAR(100) NOT NULL COMMENT '图书编号',
`lendDate` datetime NOT NULL DEFAULT NOW() COMMENT '借阅日期',
`willDate` datetime COMMENT '应归还日期',
`returnDate` datetime COMMENT '实际归还日期',
PRIMARY KEY(rid,nif,lendDate)
)COMMENT '图书借阅表';
DROP TABLE IF EXISTS `penalty`;
CREATE TABLE `penalty`(
`rid` VARCHAR(100) NOT NULL COMMENT '读者编号',
`bid` VARCHAR(100) NOT NULL COMMENT '图书编号',
`pDate` datetime NOT NULL COMMENT '罚款日期',
`pType` INT(2) COMMENT '罚款类型',
`amount` FLOAT(6) COMMENT '罚款金额',
PRIMARY KEY(rid,bid,pDate)
)COMMENT '罚款记录表';
/*给借阅表增加外键约束*/
ALTER TABLE `borrow` ADD CONSTRAINT fk_rid FOREIGN KEY(`rid`) REFERENCES `reader`(`rid`);
ALTER TABLE `borrow` ADD CONSTRAINT fk_nif FOREIGN KEY(`nif`) REFERENCES `book`(`bid`);
/*给罚款记录表增加外键约束*/
ALTER TABLE `penalty` ADD CONSTRAINT fk_rid2 FOREIGN KEY(`rid`) REFERENCES `borrow`(`rid`);
ALTER TABLE `penalty` ADD CONSTRAINT fk_bid FOREIGN KEY(`bid`) REFERENCES `book`(`bid`);
--3，使用子查询获得当前没有被读者借阅的图书信息
SELECT * FROM book WHERE bid NOT IN(SELECT nif FROM borrow );
--4，获得今年所有缴纳罚款的读者清单，要求输出的数据包括读者姓名，图书名称，罚款日期和缴纳罚金
SELECT r.rName,b.bName,p.PDate,p.amount FROM penalty AS p
INNER JOIN reader AS r ON(p.rid=r.rid)
INNER JOIN book AS b ON(p.bid=b.bid);
--5，获得地址为空的所有读者尚未归还的图书清单，要求按读者编号从高到低，借书日期由近至远输出读者编号，读者姓名，图书名称，借书日期和应归还日期
SELECT br.rid AS '读者编号',r.rName AS '读者姓名',b.bName AS '图书名称',br.lendDate AS '借书日期',br.willDate AS '应归还日期' FROM
borrow AS br INNER JOIN reader AS r ON(br.rid=r.rid)
INNER JOIN book AS b ON(br.nif=b.bid)
WHERE r.rAddress is NULL AND br.returnDate is NULL
ORDER BY br.rid DESC,br.lendDate DESC;
--第三章课后
--2
SELECT rid,rName FROM reader WHERE rid NOT IN(SELECT rid FROM borrow);
SELECT rid,rName FROM reader as r WHERE NOT EXISTS(SELECT rid FROM borrow as b WHERE r.rid=b.rid)
-3
SELECT (SELECT r.rName FROM reader AS r WHERE r.rid=b.rid) AS `读者姓名`,(SELECT bk.bName FROM book AS bk WHERE bk.bid=b.nif) AS `图书名称`,willDate
FROM (SELECT * FROM borrow WHERE willDate<NOW() AND returnDate IS NULL) AS b
--4
SELECT bk.bName,bk.bcount-(SELECT COUNT(b.nif) FROM borrow AS b WHERE b.nif=bk.bid) AS `次数` FROM book AS bk
--5
SELECT r.rName,COUNT(b.rid) FROM borrow AS b
JOIN reader AS r ON(b.rid=r.rid)
WHERE b.returnDate IS NOT NULL
GROUP BY r.rid
--6
SELECT (SELECT r.rName FROM reader AS r WHERE r.rid=p.rid) AS `姓名`,p.amount AS `罚金` FROM penalty AS p WHERE p.amount>5
--7
SELECT (SELECT bk.bName FROM book AS bk WHERE bk.bid=b.nif) AS `图书名称`,COUNT(b.nif) FROM borrow AS b WHERE b.returnDate is NOT NULL GROUP BY b.nif
LIMIT 5

--第四章课后
--3
BEGIN;
UPDATE book SET bCount=bCount-1 WHERE bName='aaa';
UPDATE reader SET lendNum=lendNum+1 WHERE rName='vvw';
INSERT INTO borrow(rid,nif,willDate) VALUES(1,'001','2017-11-09');
COMMIT
--4
BEGIN;
INSERT INTO penalty VALUES((SELECT rid FROM reader WHERE rName='vvw'),(SELECT bid FROM book WHERE bName='aaa'),NOW(),2,4.6);
UPDATE borrow SET returnDate=NOW() WHERE rid=(SELECT rid FROM reader WHERE rName='vvw') AND nif=(SELECT bid FROM book WHERE bName='aaa')
UPDATE reader SET lendNum=lendNum-1 WHERE rName='vvw'
UPDATE book SET bCount=bCount+1 WHERE bName='aaa';
COMMIT
--5
CREATE VIEW bookNum_view AS
SELECT bk.bName AS `图书名称`,bk.bCount AS `存量`,bk.bCount-(SELECT COUNT(*) FROM borrow as b WHERE b.nif=bk.bid) AS `可借阅数量` FROM book as bk
SELECT * FROM bookNum_view

CREATE VIEW admin_view AS
SELECT bk.bName AS `图书名称`,b.willdate as `到期日期`,r.rName AS '读者姓名' FROM borrow AS b
INNER JOIN book AS bk ON(b.nif=bk.bid)
INNER JOIN reader AS r ON(r.rid=b.rid)
WHERE b.willdate<=NOW() AND b.returndate IS NULL
SELECT * FROM admin_view

CREATE INDEX index_bName ON book(bName)



